import os
import json
import pandas as pd
# from pandas.io.json import json_normalize

#该函数去除文件的绝对路径和扩展名
def convert_path(absolute_path):
    # 获取文件名（包含扩展名）
    file_name_with_ext = os.path.basename(absolute_path)
    # 去除扩展名
    file_name_without_ext, _ = os.path.splitext(file_name_with_ext)
    return file_name_without_ext

# 设置包含JSON文件的目录路径
json_directory = os.getcwd()
# json_file_path = 'approval-service_trivy_result.json'

# 定义需要选择的字段
fields = [
    "VulnerabilityID", "PkgName", "PkgPath", "InstalledVersion",
    "FixedVersion", "Title", "Description", "Severity"
]

# 定义一个函数来处理嵌套字段
# def get_nested_value(data, field_path):
#     keys = field_path.split('.')
#     value = data
#     for key in keys:
#         value = value.get(key, None)  # 使用get方法避免KeyError
#     return value

# 初始化一个空的DataFrame，用于存储所有文件的数据
all_data = pd.DataFrame()

for filename in os.listdir(json_directory):
    if filename.endswith('.json'):  # 确保只处理JSON文件
        json_file_path = os.path.join(json_directory, filename)

    # 读取JSON文件
        with open(json_file_path, 'r', encoding='utf-8') as file:
            data = json.load(file)

        if "Results" in data and isinstance(data["Results"], list):
            # 初始化一个空列表来存储筛选后的数据
            filtered_data = []

            # 遍历Results列表
            for result in data["Results"]:
                # 检查result中是否包含Vulnerabilities节点，并且Vulnerabilities是一个列表
                if "Vulnerabilities" in result and isinstance(result["Vulnerabilities"], list):
                    # 遍历Vulnerabilities列表
                    for vulnerability in result["Vulnerabilities"]:
                        # 使用字典推导式筛选出需要的字段
                        # filtered_dict = {field: vulnerability.get(field) for field in fields if field in vulnerability}
                        filtered_dict = {"ImgName": convert_path(json_file_path)}
                        filtered_dict.update({field: vulnerability.get(field) for field in fields if field in vulnerability})
                        
                        if "CVSS" in vulnerability:
                            cvss_node = vulnerability.get("CVSS")
                            if "nvd" in cvss_node:
                                nvd_node = cvss_node.get("nvd")
                                filtered_dict.update({"NVD_Vector": nvd_node.get("V3Vector")})
                                filtered_dict.update({"NVD_Score": nvd_node.get("V3Score")})

                        filtered_data.append(filtered_dict)
                        # filtered_data.append(vulnerability.get("VulnerabilityID"))
        
        # print(filtered_data)
        # 将筛选后的数据转换为pandas DataFrame
        df = pd.DataFrame(filtered_data)
        all_data = pd.concat([all_data, df], ignore_index=True) # 合并数据

    # 写入Excel文件
excel_filename = 'vulnerabilities1.xlsx'
all_data.to_excel(excel_filename, index=False)

print(f'Data from {json_directory} has been written to {excel_filename}')
